package util;
/*Junwoo Kim:kim1035 | last Updated: 10.23.11 - added init() method
 *
 *Junwoo Kim:kim1035 | last Updated: 11.03.11 - added saveMeal method which is to be called when saving method
 *
 *Junwoo Kim:kim1035 | last Updated: 11.14.11 - minor changes
 *
 *Junwoo Kim:kim1035 | last Updated: 11.15.11 - completed getMeal method
 */
import java.util.*;
import java.sql.*;
import meal.*;

/**
 * A utility class that helps saving and loading the state of the application.
 *
 * DataBase class is used to save and load the state.
 * It serializes the objects to save them
 *
 */

public class DataBase {
	/*Junwoo Kim:kim1035 | last Updated: 10.23.11*/
	//connection which will be made and closed in each methods
//	private static Connection conn;
	//object which will be used to execute sqlite quaries
//	private static Statement stat;

/*
 * Junwoo Kim:kim1035 | last Updated 11.03.11
 * Task: to be called at the start of the application.
 * gets things ready
 */
/**
 * initializes the class.
 */
	public static void init() {
System.out.println("DEBUG : init started");
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
			Statement stat = conn.createStatement();
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS meal (name, rating, lastHad);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS ingredient (mealname, name, units, quantity);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS direction (mealname, step);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS units (unit);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS date (date);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS week (day0, day1, day2, day3, day4, day5, day6);");
			conn.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
System.out.println("DEBUG : init finished");
	}



/*
 * Junwoo Kim:kim1035 | last Updated 11.29.11
 * Task: saves the last generated date in 'long'
 *
 */
/**
 * Saves the date of last generation
 * @Param lastGen long variable to be just saved
 */

	public static void saveLastGen(long lastGen) throws Exception {
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();

		stat.execute("DELETE * FROM date;");

		stat.execute("INSERT INTO date (date) VALUES ('" + lastGen + "');");



		conn.close();

	}
/*
 * Junwoo Kim:kim1035 | last Updated 11.29.11
 * Task: returns the last generated date in 'long'
 *
 */
/**
 * Returns the date of last generation
 */
	public static long getLastGen() throws Exception {
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("SELECT * FROM date;");

		String dateInString = rs.getString("unit");

		return Long.parseLong(dateInString);

	}
/*
 * Junwoo Kim:kim1035 | last Updated 11.29.11
 * Task: saves a boolean array which represents the dates in a week
 */
/**
 * Saves the boolean array of dates; It assumes that the array size is 7
 * @Param dates boolean array to be saved
 */
	public static void saveWeek(boolean[] week) throws Exception {
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();

		stat.execute("DELETE * FROM date;");
		int[] array = new int[7];
		for (int i = 0; i < week.length; i++) {
			if (week[i])
				array[i] = 1;
			else
				array[i] = 0;
		}
		stat.execute("INSERT INTO week (day0, day1, day2, day3, day4, day5, day6) VALUES ('"
						+ array[0] + ", " + array[1] + ", " + array[2] + ", " + array[3] + ", " + array[4] + ", "
						+ array[5] + ", " + array[6] + "');");



		conn.close();

	}

/*
 * Junwoo Kim:kim1035 | last Updated 11.29.11
 * Task: returns a boolean array which represents the dates in a week
 */
/**
 * Returns the boolean array of dates; The size of the array is 7
 */
	public static boolean[] getWeek() throws Exception {
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();

//		ArrayList<int> result = new ArrayList<int>();
		boolean[] result = new boolean[7];
		ResultSet rs = stat.executeQuery("SELECT * FROM week;");
		for (int i = 0; i < 7; i++) {
			int temp = rs.getInt("day" + i);
			if (temp == 1) {
				result[i] = true;
			} else {
				result[i] = false;
			}

		}
		return result;
	}



/*
 * Junwoo Kim:kim1035 | last Updated 11.28.11
 * Task: saves all the units
 *
 */
/**
 * Saves all units.
 * @Param meal the Meal object to be saved
 */
	public static void saveUnits(ArrayList<String> units) throws Exception {
System.out.println("DEBUG : saveUnits started");
//		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();

		stat.execute("DELETE * FROM units;");

		for (int i = 0; i < units.size(); i++) {
			stat.execute("INSERT INTO units (unit) VALUES ('" + units.get(i) + "');");


		}
		conn.close();

System.out.println("DEBUG : saveUnits finished");

		return;
	}

/*
 * Junwoo Kim:kim1035 | last Updated 11.28.11
 * Task: gives you back all the units
 *
 */
/**
 * returns all the units in an ArrayList of Strings
 */
	public static ArrayList<String> getUnits() throws Exception {
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("SELECT * FROM units;");
		ArrayList<String> result = new ArrayList<String>();

		while (rs.next()) {
			result.add(rs.getString("unit"));

		}

		return result;
	}
/*
 * Junwoo Kim:kim1035 | last Updated 11.03.11
 * Task: Serialize and saves the meal object to sqlite database
 *
 */
/**
 * Saves a meal object by serializing it.
 * @Param meal the Meal object to be saved
 */
	public static void saveMeal(Meal meal) throws Exception {
System.out.println("DEBUG : saveMeal started");
//		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("SELECT * FROM meal WHERE name = '" + meal.getName() + "';");

		try {
			String dummy = rs.getString("name");//if an exception is not thrown here, it means that it already existed
System.out.println("DEBUG : " + dummy);
System.out.println("DEBUG : exists");
			//deletes the meal object from the database
			if (stat.execute("DELETE FROM meal WHERE name = '" + meal.getName() + "';"))
				System.out.println("fuckyou");
			if (stat.execute("DELETE FROM ingredient WHERE mealName = '" + meal.getName() + "';")) {
				System.out.println("f");;
			}

			if (stat.execute("DELETE FROM direction WHERE mealName = '" + meal.getName() + "';"))
				System.out.println("efff");

		} catch (Exception e) {
System.out.println("DEBUG : (probably deleted and)...adding now");
	//		e.printStackTrace();
		}

		stat.execute("INSERT INTO meal (name, rating, lastHad) VALUES ( '" + meal.getName() + "', " + meal.getRating() + ", " + meal.getLastHad() + ");");
		Ingredient[] ingredient = meal.getIngredients();
		for (int i = 0; i < ingredient.length; i++) {
			stat.execute("INSERT INTO ingredient (mealname, name, units, quantity) VALUES ('" + meal.getName() + "', '" + ingredient[i].getName() + "', '" + ingredient[i].getUnits() + "', '" + ingredient[i].getQuantity() + "');");
		}
		Direction[] direction = meal.getDirections();
		for (int i = 0; i < direction.length; i++) {
			stat.execute("INSERT INTO direction (mealname, step) VALUES ('" + meal.getName() + "', '" + direction[i].getStep() + "');");
		}
		rs.close();
		conn.close();

System.out.println("DEBUG : saveMeal finished");

		return;
	}
/*
 * Junwoo Kim:kim1035 | last Updated 11.14.11
 * Task: returns all meal names in an array
 *
 */
/**
 * returns all meal names in an array
 */
	public static String[] getMealName() throws Exception {
System.out.println("DEBUG : get mealname");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("SELECT * FROM meal;");
		ArrayList<String> bucket = new ArrayList<String>();
//		System.out.println(rs);
		if (rs == null) {
System.out.println("DEBUG : nothing found");
			return null;
		}
		try {
			while (rs.next()) {
System.out.println("DEBUG : print started");
				String temp = rs.getString("name");
				bucket.add(temp);
			}
		} catch (Exception e) {
			System.out.println("null");
			e.printStackTrace();
		}
		conn.close();
		rs.close();
		String[] result = new String[bucket.size()];
		for (int i = 0; i < result.length; i++) {
			result[i] = new String(bucket.get(i));
		}
//		result = (String[])bucket.toArray();

		return result;
	}

/*
 * Junwoo Kim:kim1035 | last Updated 11.14.11
 * Task: returns the meal object which corresponds to the given name
 *
 */


/**
 * returns a meal object of the name given
 * return null if there is no such meal.
 * @Param mealName the name of the meal object we are looking for
 */

	public static Meal getMeal(String mealName) throws Exception {
		Meal result = null;
		double rating = 0;
		long lastHad = 0;
		ArrayList<Ingredient> ingredientBucket = new ArrayList<Ingredient>();
		ArrayList<Direction> directionBucket = new ArrayList<Direction>();
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("SELECT * FROM meal WHERE name = '" +mealName + "';");
//		ResultSet ingredientResult = stat.executeQuery("SELECT * FROM ingredient WHERE mealName = '" + mealName + "';");
		try {
			rating = rs.getDouble("rating");
			lastHad = rs.getLong("lastHad");
			rs.close();//get the raring from meal table
			System.out.println(rating);
			//get the ingredients and add them to the ingredientBucket. later used to create meal object
			rs = stat.executeQuery("SELECT * FROM ingredient WHERE mealname = '" + mealName + "';");
			while (rs.next()) {
				String name = rs.getString("name");
				String units = rs.getString("units");
				int quantity = rs.getInt("quantity");
				Ingredient temp = new Ingredient(name, quantity,  units);
				ingredientBucket.add(temp);
			}
			rs.close();
			rs = stat.executeQuery("SELECT * FROM direction WHERE mealname = '" + mealName + "';");
			while (rs.next()) {
				String step = rs.getString("step");
				Direction temp = new Direction(step);
				directionBucket.add(temp);
			}
			rs.close();

			Ingredient[] ingredient = new Ingredient[ingredientBucket.size()];
			for (int i = 0; i < ingredient.length; i++) {
				ingredient[i] = ingredientBucket.get(i);
			}

			Direction[] direction = new Direction[directionBucket.size()];
			for (int i = 0; i < direction.length; i++) {
				direction[i] = directionBucket.get(i);
			}
			result = new Meal(mealName, ingredient, direction, rating, lastHad);

			conn.close();
			return result;

//			ingredientResult.close();
/* ingredient (mealname, name, units, quantity);");
			stat.executeUpdate("CREATE TABLE IF NOT EXISTS direction (mealname, step);");
 */
		} catch (Exception e) {
			e.printStackTrace();
		}
		rs.close();
		conn.close();
	//	mealResult.close();
//		ingredientResult.close();
		return null;
	}


/*
 * Junwoo Kim:kim1035 | last Updated 11.28.11
 * Task: Deletes the meal object in the db
 *
 */

/**
 * Deletes a meal object in the db.
 * @Param meal the Meal object to be deleted
 **/

	public static boolean deleteMeal(Meal meal) throws Exception {
System.out.println("DEBUG : deleteMeal started");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		ResultSet rs = null;

		try {
	//		rs = stat.executeQuery("SELECT * FROM meal WHERE name = '" + meal.getName() + "';");

//			String dummy = rs.getString("name");//if an exception is not thrown here, it means that it already existed
//System.out.println("DEBUG : " + dummy);
System.out.println("DEBUG : exists");
			//deletes the meal object from the database
			stat.executeQuery("DELETE FROM meal WHERE name = '" + meal.getName() + "';");
			System.out.println("This does not gets printed out");
			stat.execute("DELETE FROM ingredient WHERE name = '" + meal.getName() + "';");
			stat.execute("DELETE FROM direction WHERE name = '" + meal.getName() + "';");

		} catch (Exception e) {
System.out.println("DEBUG : the meal object you're trying to erase does not exists. returning false");
			e.printStackTrace();
//			rs.close();
			conn.close();
			return false;
		}

	//	rs.close();
		conn.close();

		return true;
	}


/*
	public static boolean deleteMealwithName(String mealName) throws Exception {
System.out.println("DEBUG : deleteMeal started");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:mealData.db");
		Statement stat = conn.createStatement();
		Meal meal = null;
		try {
			meal = getMeal(mealName);
		} catch (NullPointerException e) {
System.out.println("DEBUG : meal you're trying to delete does not exists");
			conn.close();
			return false;
		}
		ResultSet rs = stat.executeQuery("SELECT * FROM meal WHERE name = '" + meal.getName() + "';");

		try {
			String dummy = rs.getString("name");//if an exception is not thrown here, it means that it already existed
System.out.println("DEBUG : " + dummy);
System.out.println("DEBUG : exists");
			//deletes the meal object from the database
			stat.executeQuery("DELETE FROM meal WHERE name = '" + meal.getName() + "';");
			stat.executeQuery("DELETE FROM ingredient WHERE name = '" + meal.getName() + "';");
			stat.executeQuery("DELETE FROM direction WHERE name = '" + meal.getName() + "';");

		} catch (Exception e) {
System.out.println("DEBUG : the meal object you're trying to erase does not exists. returning false");
//			e.printStackTrace();
			rs.close();
			conn.close();
			return false;
		}

		rs.close();
		conn.close();

		return true;
	}
	*/
}
//		PreparedStatement prep = conn.prepareStatement("insert into meal values(?);)");
//		prep.setString(1, meal.getName);


